package jdbc;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Scanner;

/**
 * 根据输入的老师名字，查看该老师的名字，工资，职位，所带班级的名字，楼层
 * 以及班里每个学生的名字，年龄，性别
 */
public class JDBCDemo13 {
    public static void main(String[] args) {
        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入老师名字");
        String name = scanner.nextLine();
        try (Connection connection = DBUtil.getConnection();){
            String sql = "SELECT t.name,t.salary,t.title,c.name,c.floor,s.name,s.age,s.gender " +
                         "FROM teacher t " +
                         "JOIN class c ON t.id=c.teacher_id " +
                         "JOIN student s ON c.id=s.class_id " +
                         "WHERE t.name=?";
            PreparedStatement statement = connection.prepareStatement(sql);
            statement.setString(1, name);
            ResultSet rs = statement.executeQuery();
            while(rs.next()){
                String tName = rs.getString("t.name");
                int tSalary = rs.getInt("t.salary");
                String tTitle = rs.getString("t.title");
                String cName = rs.getString("c.name");
                int cFloor = rs.getInt("c.floor");
                String sName = rs.getString("s.name");
                int sAge = rs.getInt("s.age");
                String sGender = rs.getString("s.gender");
                System.out.println(tName+"--"+tSalary+"--"+tTitle+"--"+cName+"--"+cFloor+"--"+sName+"--"+sAge+"--"+sGender);
            }


        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
